Imports System.Data.Common
Imports System.Net.Mail
Partial Class Presentation_ReportSubmit
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        
        Dim Records As DbDataReader
        Dim Sql As String
        Dim NoPerson As New ListItem

        Page.Title = "Management Reports Entry"

        If Not Page.IsPostBack Then


            Sql = "SELECT DISTINCT Email, ExecutiveName FROM (ExecutiveMaster INNER JOIN (RoleMaster INNER JOIN DepartmentMaster ON DepartmentMaster.DepartmentID = RoleMaster.DepartmentID)" & _
            " ON ExecutiveMaster.ExecutiveID = RoleMaster.ExecutiveID)" & _
            " WHERE (DepartmentName = 'Administration' OR DepartmentName = 'Top Management') AND Active = 1" & _
            " ORDER BY ExecutiveName"
            Records = GenHelper.ExecuteReader(Sql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            dlExtraPerson.DataTextField = "ExecutiveName"
            dlExtraPerson.DataValueField = "Email"
            NoPerson.Text = "Select a person (Optional)"
            NoPerson.Value = "0"
            NoPerson.Selected = True
            dlExtraPerson.DataSource = Records
            dlExtraPerson.DataBind()
            dlExtraPerson.Items.Add(NoPerson)
            Records.Close()


        End If

        

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim RightNow As Date
        Dim TDate As String
        Dim Sql As String
        Dim Params(1) As GenParameter
        Dim Value As Integer
        Dim ReportType As String = SelectReport.SelectedValue


        'Insert the Correct Date

        RightNow = Now.AddHours(10)
        RightNow = RightNow.AddMinutes(30)
        TDate = RightNow.Year & "/" & RightNow.Month & "/" & RightNow.Day

        'Check if the Reports already exist in the Database

        Select Case ReportType

            Case "Daily"

                Sql = "SELECT COUNT(*) FROM ReportMaster WHERE ExecutiveID = ?ExecutiveID" & _
                " AND ReportDate = ?ReportDate AND ReportType = 'Daily'"

                Params(0) = New GenParameter("?ExecutiveID", Request.Cookies("Profile").Item("ExecutiveID"), Data.DbType.UInt32)
                Params(1) = New GenParameter("?ReportDate", TDate, Data.DbType.Date)

                Value = GenHelper.ExecuteScalar(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            Case "Weekly"

                Sql = "SELECT COUNT(*) FROM ReportMaster WHERE ExecutiveID = ?ExecutiveID" & _
                    " AND Week(ReportDate) = Week(?TodaysDate) AND Year(ReportDate) = Year(?TodaysDate)" & _
                    " AND ReportType = 'Weekly'"

                Params(0) = New GenParameter("?ExecutiveID", Request.Cookies("Profile").Item("ExecutiveID"), Data.DbType.UInt32)
                Params(1) = New GenParameter("?TodaysDate", TDate, Data.DbType.Date)

                Value = GenHelper.ExecuteScalar(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            Case "Monthly"

                Sql = "SELECT COUNT(*) FROM ReportMaster WHERE ExecutiveID = ?ExecutiveID" & _
                    " AND Month(ReportDate) = Month(?TodaysDate) AND Year(ReportDate) = Year(?TodaysDate)" & _
                    " AND ReportType = 'Monthly'"

                Params(0) = New GenParameter("?ExecutiveID", Request.Cookies("Profile").Item("ExecutiveID"), Data.DbType.UInt32)
                Params(1) = New GenParameter("?TodaysDate", TDate, Data.DbType.Date)

                Value = GenHelper.ExecuteScalar(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())


        End Select



        If Value <> 0 Then
            lblExists.Visible = True
            lblSaved.Visible = False
            Return
        Else
            lblExists.Visible = False
        End If

        'Send a mail

        Dim Mail As New MailMessage()

        Mail.From = New MailAddress("GROND@zakgroup.com")
        Mail.To.Add("mktg@zakgroup.com")
        Mail.CC.Add("bhagwad@zakgroup.com")

        'Add the person selected in the list
        If dlExtraPerson.SelectedValue <> "0" Then
            Mail.CC.Add(dlExtraPerson.SelectedValue)
        End If
        Mail.Subject = ReportType & " Report from " & Request.Cookies("Profile").Item("ExecutiveName") & " - Generated by Grond"

        Mail.Body = txtReport.Text

        Dim smtp As New SmtpClient("127.0.0.1")
        smtp.Send(Mail)

        'Make the Database Entries

        Sql = "INSERT INTO ReportMaster (ExecutiveID, ReportDate, ReportText, ReportType)" & _
            " VALUES (?ExecutiveID, ?ReportDate, ?ReportText, ?ReportType)"
        ReDim Params(3)

        Params(0) = New GenParameter("?ExecutiveID", Request.Cookies("Profile").Item("ExecutiveID"), Data.DbType.UInt32)
        Params(1) = New GenParameter("?ReportDate", TDate, Data.DbType.Date)
        Params(2) = New GenParameter("?ReportText", txtReport.Text, Data.DbType.String)
        Params(3) = New GenParameter("?ReportType", ReportType, Data.DbType.String)

        GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        lblSaved.Visible = True

        ' Send an Email to mktg

        

        'Clear the Report Box

        txtReport.Text = ""


    End Sub
End Class
